package com.icesoft.core.web.helper.excel;

import java.io.File;
import java.io.FileOutputStream;
import java.io.IOException;
import java.util.Collection;
import java.util.LinkedHashMap;
import java.util.List;
import java.util.Map;

import org.apache.commons.io.FileUtils;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFRichTextString;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellType;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;

import com.icesoft.core.web.helper.excel.SaxExcelReader;

import lombok.extern.slf4j.Slf4j;

@Slf4j
public class ExcelUtil {
	/**
	 * 从excel读取数据
	 * 
	 * @param filepath
	 *            excel文件路径
	 */
	public static final Collection<Map<String, String>> readFromExcel(String filepath) throws IOException {
		return readFromExcel(new File(filepath));
	}

	/**
	 * 从excel读取数据
	 * 
	 */
	public static final Collection<Map<String, String>> readFromExcel(File file) throws IOException {
		return SaxExcelReader.readExcelContent(file);
	}

	/**
	 * 保存数据到excel
	 * 
	 * @param data
	 *            数据
	 * @param pathAndName
	 *            excel保存路径，已存在会覆盖
	 */
	public static final void writeToExcel(List<Map<String, String>> data, String pathAndName) throws IOException {
		writeToExcel(data, pathAndName, new CellHandle());
	}

	public static final void writeToExcel(List<Map<String, String>> data, File file) throws IOException {
		writeToExcel(data, file, new CellHandle());
	}

	/**
	 * 保存数据到excel
	 * 
	 * @param data
	 *            数据
	 * @param pathAndName
	 *            excel保存路径，已存在会覆盖
	 * @param cellHandle
	 *            excel单元格处理，自定义样式等操作
	 */
	public static final void writeToExcel(List<Map<String, String>> data, String pathAndName, CellHandle cellHandle)
			throws IOException {
		writeToExcel(data, new File(pathAndName), cellHandle);
	}

	public static final void writeToExcel(List<Map<String, String>> data, File file, CellHandle cellHandle)
			throws IOException {
		try (HSSFWorkbook workbook = writeToWorkbook(data, cellHandle);
				FileOutputStream out = FileUtils.openOutputStream(file)) {
			workbook.write(out);
			workbook.close();
			out.close();
		}
	}

	public static class CellHandle {
		private List<Map<String, String>> data;

		/**
		 * 执行导出前调用
		 */
		public void prepare(Workbook workbook, Sheet sheet) {

		}

		/**
		 * 每一行开始调用
		 */
		public void startRow(Row row, int rowIndex) {

		}

		/**
		 * 每一行结束调用
		 */
		public void endRow(Row row, int rowIndex) {

		}

		/**
		 * 每一个单元格设置数据后调用
		 */
		public void cell(String title, String value, Cell cell) {

		}

		/**
		 * 每一个单元格设置数据后调用
		 */
		public void cell(String title, String value, Cell cell, Workbook workbook) {
			cell(title, value, cell);
		}

		/**
		 * 处理结束后调用
		 */
		public void finish(HSSFWorkbook workbook, Sheet sheet) {
			int columns = data.size();
			// 必须在单元格设值以后进行
			// 设置为根据内容自动调整列宽
			for (int k = 0; k < columns; k++) {
				sheet.autoSizeColumn(k);
			}
		}

		public final List<Map<String, String>> getData() {
			return data;
		}
	}

	private static final HSSFWorkbook writeToWorkbook(List<Map<String, String>> data, CellHandle cellHandle)
			throws IOException {
		// 创建新的Excel 工作簿
		HSSFWorkbook workbook = new HSSFWorkbook();
		// 在Excel工作簿中建一工作表，其名为缺省值
		HSSFSheet sheet = workbook.createSheet("导出数据");
		if (data.isEmpty()) {
			log.debug("无数据，导出空excel");
			return workbook;
		}
		Map<String, String> headMap = new LinkedHashMap<>();
		data.get(0).keySet().forEach(key -> {
			headMap.put(key, key);
		});
		data.add(0, headMap);
		cellHandle.data = data;
		cellHandle.prepare(workbook, sheet);
		int rowNum = data.size();
		if (log.isTraceEnabled()) {
			log.trace("导出{}行数据:{}", rowNum, data);
		}
		for (int i = 0; i < rowNum; i++) {
			Map<String, String> map = data.get(i);
			HSSFRow row = sheet.createRow(i);
			cellHandle.startRow(row, i);
			int j = 0;
			for (Map.Entry<String, String> entry : map.entrySet()) {
				String value = entry.getValue();
				HSSFCell cell = row.createCell(j);
				if (value == null || value.trim().equals("")) {
					cell.setCellType(CellType.STRING);
					cell.setCellValue("");
				} else {
					HSSFRichTextString hr = new HSSFRichTextString(value);
					cell.setCellValue(hr);
				}
				cellHandle.cell(entry.getKey(), value, cell, workbook);
				j++;
			}
			cellHandle.endRow(row, i);
		}
		cellHandle.finish(workbook, sheet);
		return workbook;
	}
}
